import com.lefu.utils.common.excelhelper.ExcelRead;
import com.lefu.utils.common.excelhelper.ExcelWrite;
import com.lefu.utils.common.exception.LefuException;
import org.apache.commons.lang.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * 产品信息bean
 */
class ProductInfo {
    private Integer id; // 商品id
    private Integer p_id; // 父级商品id
    private Integer classID; // 分类ID
    private Integer brandID; // 品牌ID
    private BigDecimal price_market; // 市场价格
    private String title; // 产品名称
    private String child_title; // 商品子标题
    private String feature; // 特点
    private String spec; // 规格
    private String detail; // 商品详情表
    private String images; // 商品轮播图
    private Integer specID; // 商品对应规格id
    private String extProductId; // 外部产品id（以便之后对应使用）

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getP_id() {
        return p_id;
    }

    public void setP_id(Integer p_id) {
        this.p_id = p_id;
    }

    public Integer getClassID() {
        return classID;
    }

    public void setClassID(Integer classID) {
        this.classID = classID;
    }

    public Integer getBrandID() {
        return brandID;
    }

    public void setBrandID(Integer brandID) {
        this.brandID = brandID;
    }

    public BigDecimal getPrice_market() {
        return price_market;
    }

    public void setPrice_market(String price_market) {
        this.price_market = parseBigDecimal(price_market);

    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getChild_title() {
        return child_title;
    }

    public void setChild_title(String child_title) {
        this.child_title = child_title;
    }

    public String getFeature() {
        return feature;
    }

    public void setFeature(String feature) {
        this.feature = feature;
    }

    public String getSpec() {
        return spec;
    }

    public void setSpec(String spec) {
        this.spec = spec;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    public String getImages() {
        return images;
    }

    public void setImages(String images) {
        this.images = images;
    }

    public Integer getSpecID() {
        return specID;
    }

    public void setSpecID(Integer specID) {
        this.specID = specID;
    }

    public String getExtProductId() {
        return extProductId;
    }

    public void setExtProductId(String extProductId) {
        this.extProductId = extProductId;
    }

    /**
     * 转换为 BigDecimal
     * @param bigDecimal
     * @return
     */
    private BigDecimal parseBigDecimal(String bigDecimal) {
        try {
            return new BigDecimal(bigDecimal);
        } catch (NumberFormatException ex) {

        }
        return new BigDecimal(-1);
    }

}

/**
 * ASone产品导入
 * Created by lrs on 2018/3/8.
 */
public class Import {
    static HashMap<String, Integer> classMap = new HashMap<String, Integer>(); // 分类数据
    static HashMap<String, Integer> classRootMap = new HashMap<String, Integer>(); // 一级分类数据
    static HashMap<String, Integer> brandMap = new HashMap<String, Integer>(); // 品牌数据

    /**
     * 开始导入数据
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @throws Exception
     */
    public void start(String url, String user, String password) throws Exception {
        System.out.println("开始导入数据......");

        loadClass(url, user, password); // 装载分类数据
        loadBrand(url, user, password); // 装载品牌数据

        FileOutputStream fos = new FileOutputStream("./output/Product_err.xls"); // 将 subCategory_LsTypeSysNo_6_SysNo_2.xls 文件名生成类似 Product_LsTypeSysNo_100_SysNo_60.xls 的文件名保存产品信息
        ExcelWrite excelWrite = new Product().createProductExcel();

        int row = 1;

        // 读取主分类excel文件
        File file = new File("./output/Product.xls");
        FileInputStream fis = new FileInputStream(file);
        ExcelRead excelReader = new ExcelRead(fis);
        while (excelReader.next()) {
            System.out.print("导入第 " + row++ + " 条，");
            Integer classID = getClassID(excelReader.getString("一级分类"), excelReader.getString("二级分类"), excelReader.getString("三级分类"));
            if (null == classID) {
                ArrayList<String> list = excelReader.getCurrentData();
                list.add("分类ID未找到！");
                excelWrite.addBody(list); // 分类未找到输出错误
                System.out.println("错误：分类ID未找到！");
            } else {
                Integer brandID = getBrandID(excelReader.getString("一级分类"), excelReader.getString("品牌"));
                if (null == brandID) {
                    ArrayList<String> list = excelReader.getCurrentData();
                    list.add("品牌未找到！");
                    excelWrite.addBody(list); // 品牌未找到输出错误
                    System.out.println("错误：品牌未找到！");
                } else {
                    try {
                        insertProduct(url, user, password, excelReader, classID, brandID); //  插入一个产品数据
                        System.out.println("成功！");
                    } catch (Exception e) {
                        ArrayList<String> list = excelReader.getCurrentData();
                        list.add(getExceptionMessage(e));
                        excelWrite.addBody(list); // 错误
                        System.out.println("错误：" + getExceptionMessage(e));
                    }
                }
            }
        }

        excelWrite.write(fos);
        fos.close();
        System.out.println("导入数据成功！！！.");
    }

    /**
     * 截取错误信息
     * @param e
     * @return
     */
    private String getExceptionMessage(Exception e) {
        String msg = e.getMessage();
        if (msg.length() > 300) {
            return msg.substring(0, 300);
        } else {
            return msg;
        }
    }

    /**
     * 插入一个产品数据
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param excelReader 产品数据
     * @param classID 分类id
     * @param brandID 品牌id
     * @throws LefuException
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private void insertProduct(String url, String user, String password, ExcelRead excelReader, Integer classID, Integer brandID) throws LefuException, SQLException, ClassNotFoundException, IOException {
        ProductInfo productInfo = new ProductInfo();
        productInfo.setClassID(classID);
        productInfo.setBrandID(brandID);
        productInfo.setPrice_market(excelReader.getString("产品单价"));
        productInfo.setTitle(excelReader.getString("产品名"));
        productInfo.setChild_title(excelReader.getString("产品子名称"));
        productInfo.setFeature(excelReader.getString("特点"));
        productInfo.setSpec(excelReader.getString("规格"));
        productInfo.setExtProductId("asone:" + excelReader.getString("产品ID"));

        // 插入产品数据 父产品
        if (!insertProductToDatabase(url, user, password, productInfo)) {
            throw new LefuException("父产品数据插入错误！");
        }

        int pid = productInfo.getId();

        // 插入产品数据 子产品
        productInfo.setP_id(pid);
        if (!insertChildProductToDatabase(url, user, password, productInfo)) {
            throw new LefuException("子产品数据插入错误！");
        }

        // 插入产品详情数据
        productInfo.setDetail(new FileHelp().readFile(("./output/desc/" + excelReader.getString("产品ID") + ".html")));
        if (!insertProductDetailToDatabase(url, user, password, productInfo)) {
            throw new LefuException("产品详情数据插入错误！");
        }

        // 插入产品轮播图数据
        productInfo.setImages(excelReader.getString("轮播图URL"));
        if (!insertProductImagesToDatabase(url, user, password, productInfo)) {
            throw new LefuException("产品轮播图数据插入错误！");
        }

        // 插入产品对应规格数据
        productInfo.setSpecID(475);
        if (!insertProductSpecToDatabase(url, user, password, productInfo)) {
            throw new LefuException("产品对应规格数据插入错误！");
        }
    }

    /**
     * 产品信息插入数据库
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param productInfo 产品信息
     * @return
     * @throws LefuException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private boolean insertProductToDatabase(String url, String user, String password, ProductInfo productInfo) throws LefuException, ClassNotFoundException, SQLException {
        boolean ret = false;
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
        Connection conn = DriverManager.getConnection(url + "?useUnicode=true&characterEncoding=UTF-8", user, password); // 连续数据库
        if (!conn.isClosed()) {
            String sql = "INSERT INTO db_goods (brand_id, class_id, price_market, title, child_title, feature, spec, ext_product_id) VALUE (?, ?, ?, ?, ?, ?, ?, ?);";
            PreparedStatement pstmt = conn.prepareStatement(sql, new String[]{"id"}); // statement用来执行SQL语句
            pstmt.setInt(1, productInfo.getBrandID());
            pstmt.setInt(2, productInfo.getClassID());
            pstmt.setBigDecimal(3, productInfo.getPrice_market());
            pstmt.setString(4, productInfo.getTitle());
            pstmt.setString(5, productInfo.getChild_title());
            pstmt.setString(6, productInfo.getFeature());
            pstmt.setString(7, productInfo.getSpec());
            pstmt.setString(8, productInfo.getExtProductId());

            if (pstmt.executeUpdate() > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    productInfo.setId(rs.getInt(1));
                    ret = true;
                }
            }
            pstmt.close();
        }
        conn.close();

        return ret;
    }

    /**
     * 子产品信息插入数据库
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param productInfo 产品信息
     * @return
     * @throws LefuException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private boolean insertChildProductToDatabase(String url, String user, String password, ProductInfo productInfo) throws LefuException, ClassNotFoundException, SQLException {
        boolean ret = false;
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
        Connection conn = DriverManager.getConnection(url + "?useUnicode=true&characterEncoding=UTF-8", user, password); // 连续数据库
        if (!conn.isClosed()) {
            String sql = "INSERT INTO db_goods (brand_id, class_id, price_market, title, child_title, feature, spec, p_id, ext_product_id) VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?);";
            PreparedStatement pstmt = conn.prepareStatement(sql, new String[]{"id"}); // statement用来执行SQL语句
            pstmt.setInt(1, productInfo.getBrandID());
            pstmt.setInt(2, productInfo.getClassID());
            pstmt.setBigDecimal(3, productInfo.getPrice_market());
            pstmt.setString(4, productInfo.getTitle());
            pstmt.setString(5, productInfo.getChild_title());
            pstmt.setString(6, productInfo.getFeature());
            pstmt.setString(7, productInfo.getSpec());
            pstmt.setInt(8, productInfo.getP_id());
            pstmt.setString(9, productInfo.getExtProductId());

            if (pstmt.executeUpdate() > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    productInfo.setId(rs.getInt(1));
                    ret = true;
                }
            }
            pstmt.close();
        }
        conn.close();

        return ret;
    }

    /**
     * 插入产品详情
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param productInfo 产品信息
     * @return
     * @throws LefuException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private boolean insertProductDetailToDatabase(String url, String user, String password, ProductInfo productInfo) throws LefuException, ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
        Connection conn = DriverManager.getConnection(url + "?useUnicode=true&characterEncoding=UTF-8", user, password); // 连续数据库
        if (!conn.isClosed()) {
            String sql = "INSERT INTO db_goods_detail (detail, goods_id, update_time) VALUE (?, ?, ?);";
            PreparedStatement pstmt = conn.prepareStatement(sql); // statement用来执行SQL语句
            pstmt.setString(1, productInfo.getDetail());
            pstmt.setInt(2, productInfo.getP_id());
            pstmt.setInt(3, 0);

            pstmt.execute();
            pstmt.close();
        }
        conn.close();

        return true;
    }

    /**
     * 插入产品轮播图
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param productInfo 产品信息
     * @return
     * @throws LefuException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private boolean insertProductImagesToDatabase(String url, String user, String password, ProductInfo productInfo) throws LefuException, ClassNotFoundException, SQLException {
        if (!StringUtils.isEmpty(productInfo.getImages())) {
            Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
            Connection conn = DriverManager.getConnection(url + "?useUnicode=true&characterEncoding=UTF-8", user, password); // 连续数据库
            for (String img : productInfo.getImages().split("\\|")) {
                if (!StringUtils.isEmpty(img)) {
                    if (!conn.isClosed()) {
                        // 插入正常图
                        String sql = "INSERT INTO db_goods_images (pic_url,goods_id, status, is_thumb) VALUE (?, ?, 1, 0);";
                        PreparedStatement pstmt = conn.prepareStatement(sql); // statement用来执行SQL语句
                        pstmt.setString(1, img);
                        pstmt.setInt(2, productInfo.getP_id());

                        pstmt.execute();

                        // 插入缩率图
                        sql = "INSERT INTO db_goods_images (pic_url,goods_id, status, is_thumb) VALUE (?, ?, 1, 1);";
                        pstmt = conn.prepareStatement(sql); // statement用来执行SQL语句
                        pstmt.setString(1, img);
                        pstmt.setInt(2, productInfo.getP_id());

                        pstmt.execute();
                        pstmt.close();
                    }
                }
            }

            conn.close();
        }

        return true;
    }

    /**
     * 插入产品规格数据
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     * @param productInfo 产品信息
     * @return
     * @throws LefuException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private boolean insertProductSpecToDatabase(String url, String user, String password, ProductInfo productInfo) throws LefuException, ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
        Connection conn = DriverManager.getConnection(url + "?useUnicode=true&characterEncoding=UTF-8", user, password); // 连续数据库
        if (!conn.isClosed()) {
            String sql = "INSERT INTO db_spec_goods_price (price, preferential, store_count, sku, goods_id, `key`) VALUE (?, ?, 100, '', ?, ?);";
            PreparedStatement pstmt = conn.prepareStatement(sql); // statement用来执行SQL语句
            pstmt.setBigDecimal(1, productInfo.getPrice_market());
            pstmt.setBigDecimal(2, productInfo.getPrice_market());
            pstmt.setInt(3, productInfo.getId());
            pstmt.setInt(4, productInfo.getSpecID());

            pstmt.execute();
            pstmt.close();
        }
        conn.close();

        return true;
    }

    /**
     * 装载分类数据
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     */
    private void loadClass(String url, String user, String password) {
        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
            Connection conn = DriverManager.getConnection(url, user, password); // 连续数据库
            if (!conn.isClosed()) {
                Statement statement = conn.createStatement(); // statement用来执行SQL语句

                // 查询三级分类sql语句
                String sql = "SELECT bb.*, c.id AS c_id, c.class_name AS c_class_name, c.fid AS c_fid FROM `db_goods_class` AS c LEFT JOIN (SELECT aa.*, b.id AS b_id, b.class_name AS b_class_name, b.fid AS b_fid FROM `db_goods_class` AS b LEFT JOIN (SELECT a.id AS a_id, a.class_name AS a_class_name, a.fid AS a_fid FROM `db_goods_class` AS a WHERE fid=0) AS aa ON b.fid=aa.a_id WHERE a_id IS NOT NULL) AS bb ON c.fid=bb.b_id WHERE a_id IS NOT NULL";

                ResultSet rs = statement.executeQuery(sql); // 结果集
                while (rs.next()) {
                    classMap.put(rs.getString("a_class_name") + "|&|" + rs.getString("b_class_name") + "|&|" + rs.getString("c_class_name"), rs.getInt("c_id"));
                }
                rs.close();
            }
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回分类id
     * @param class_1 一级分类
     * @param class_2 二级分类
     * @param class_3 三级分类
     * @return
     */
    private Integer getClassID(String class_1, String class_2, String class_3) {
        return classMap.get(class_1 + "|&|" + class_2 + "|&|" + class_3);
    }


    /**
     * 装载品牌数据
     * @param url mysql数据库地址
     * @param user mysql用户名
     * @param password mysql密码
     */
    private void loadBrand(String url, String user, String password) {
        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
            Connection conn = DriverManager.getConnection(url, user, password); // 连续数据库
            if (!conn.isClosed()) {
                Statement statement = conn.createStatement(); // statement用来执行SQL语句

                // 查询品牌sql语句
                String sql = "SELECT id, brand_name, goods_class_id FROM `db_brand`;";

                ResultSet rs = statement.executeQuery(sql); // 结果集
                while (rs.next()) {
                    brandMap.put("|&|" + rs.getString("goods_class_id") + "|&|" + rs.getString("brand_name") + "|&|", rs.getInt("id"));
                }
                rs.close();
            }
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
            Connection conn = DriverManager.getConnection(url, user, password); // 连续数据库
            if (!conn.isClosed()) {
                Statement statement = conn.createStatement(); // statement用来执行SQL语句

                // 查询一级分类sql语句
                String sql = "SELECT id, class_name FROM `db_goods_class`;";

                ResultSet rs = statement.executeQuery(sql); // 结果集
                while (rs.next()) {
                    classRootMap.put(rs.getString("class_name"), rs.getInt("id"));
                }
                rs.close();
            }
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回品牌id
     * @param classRootName 一级分类名称
     * @param brand_name 品牌名称
     * @return
     */
    private Integer getBrandID(String classRootName, String brand_name) {
        Integer class_id = classRootMap.get(classRootName);
        if (null == class_id) {
            return null;
        } else {
            return brandMap.get("|&|" + class_id + "|&|" + brand_name + "|&|");
        }
    }

}
